﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using TerryDong.Core;
namespace TerryDong.Data
{
    /// <summary>
    /// DAL层通用操作数据库类
    /// 包括：增 删 改 查 等操作
    /// get（done）:
    ///     根据主健查找一个实体（所有字段）
    ///     根据主健查找一个实体（部分字段）
    ///     根据条件查找批量实体（返回全部字段)
    ///     根据条件查找批量实体（返回部分字段)
    ///     根据条件，返回datatable
    /// add（done）:
    ///     增加实体(不包含自增长列，可包含Null的属性)
    ///     增加实体（包含自增长列，可包含Null的属性）
    /// update(done): 根据主健更新实体（不包含标识列)
    ///         根据主健更新实体(包含自增长列，自增长列要排除在更新属性中)
    ///         根据条件更新实体（不包含标识列）
    ///         根据条件更新实体(包含标识列，标识列排除在外）
    /// delete: 根据主健删除实体    no need to implement
    ///         根据条件批量删除实体
    ///         清空表记录
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class DataContext<T> : IDataContext<T>
    {
        #region fileds
        private const string c_TableNull = "_tableName is null or empty";
        private const string c_WhereNull = "where condition is null or empty";
        private const string c_PrimaryKeyNull = "primary key name is null or empty";

        private static DataContext<T> _DataConText = null;

        private string _ConnectionString = string.Empty;
        private string _tableName = string.Empty;
        private DbSqlServer _dbHelper = null;
        #endregion

        #region constructor
        private DataContext(string conn,string tableName)
        {
            _tableName = tableName;
            this._ConnectionString = ConfigHelper.GetConnectionString(conn);
            _dbHelper = new DbSqlServer(this._ConnectionString);
        }

        /// <summary>
        /// 单键模式创建DataContext实例
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static DataContext<T> CreateInstance(string connectionString,string tableName)
        {
            
            if (_DataConText == null)
            {
                _DataConText = new DataContext<T>(connectionString,tableName);
            }
            else
            {
                if (_DataConText._ConnectionString != connectionString)
                {
                    _DataConText = new DataContext<T>(connectionString,tableName);
                }
            }
            return _DataConText;
        }

        #endregion

        #region methods

        #region get
        /// <summary>
        /// 根据主健查询实体（*）
        /// </summary>
        /// <param name="_tableName">表名</param>
        /// <param name="primaryKey">主健</param>
        /// <param name="model">实体对象要求只包含primaryKey对应的值</param>
        /// <returns>返回实体，未查到返回default(T)</returns>
        public T GetModelByPrimaryKey(string primaryKey,T model)
        {

            return GetModelByPrimaryKey(primaryKey, model, null);
        }

        /// <summary>
        /// 根据主健查询实体
        /// </summary>
        /// <param name="_tableName">表名</param>
        /// <param name="primaryKey">主健</param>
        /// <param name="model">实体对象要求只包含primaryKey对应的值</param>
        /// <param name="toSelectedColumns">要查询的列，Null默认为*</param>
        /// <returns>返回实体，未查到返回default(T)</returns>
        public T GetModelByPrimaryKey(string primaryKey, T model, string[] toSelectedColumns)
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                throw new Exception(c_TableNull);
            }

            if (string.IsNullOrEmpty(primaryKey))
            {
                throw new Exception(c_PrimaryKeyNull);
            }

            string columns = string.Empty;
            if (toSelectedColumns==null)
            {
                columns = "*";
            }
            else
            {
                //get valid columns
                List<string> validColumns = DataUtility.GetValidPropertyName<T>(model, toSelectedColumns);

                if (validColumns.Count < 1)
                {
                    throw new Exception("there is no valid columns to selected.");
                }

                for (int i = 0; i < validColumns.Count; i++)
                {
                    if (i<validColumns.Count-1)
                    {
                        columns += validColumns[i] + ",";
                    }
                    else
                    {
                        columns += validColumns[i];
                    }
                }
            }

            string sql = string.Format("SELECT {0} FROM {1} WHERE {2}=@{2}", columns,_tableName,primaryKey);
            DbParameter parameter = Activator.CreateInstance<SqlParameter>();
            parameter.ParameterName = primaryKey;
            parameter.Value = model.GetType().GetProperty(primaryKey).GetValue(model, null);

            DbDataReader dr = _dbHelper.ExecuteRow(sql, new DbParameter[] {parameter});

            if (dr != null)
            {
                model = DataUtility.SetModelValueByDataReader<T>(dr);

                dr.Close();

                return model;
            }
            else
            {
                return default(T);
            }

        }

        /// <summary>
        /// batch getting data from table, * from table.
        /// </summary>
        /// <param name="_tableName">db table name</param>
        /// <param name="whereCondition">where condition</param>
        /// <returns>return the get models</returns>
        public IList<T> GetModels(string whereCondition)
        {
            return GetModels(null, whereCondition);
        }

        public IList<T> GetModels(string[] toSelectedColumns, string whereCondition)
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                throw new Exception(c_TableNull);
            }

            if (string.IsNullOrEmpty(whereCondition))
            {
                throw new Exception(c_WhereNull);
            }

            //get valid columns
            T model = Activator.CreateInstance<T>();

            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");

            if (toSelectedColumns==null)
            {
                sb.Append("*");
            }
            else
            {
                List<string> validColumns = DataUtility.GetValidPropertyName<T>(model, toSelectedColumns);
                if (validColumns.Count < 1)
                {
                    throw new Exception("there is no valid columns to selected.");
                }
                for (int i = 0; i < validColumns.Count; i++)
                {
                    if (i < validColumns.Count - 1)
                    {
                        sb.Append(string.Concat(toSelectedColumns[i], ","));
                    }
                    else
                    {
                        sb.Append(toSelectedColumns[i]);
                    }
                }
            }
          
            sb.Append(" FROM ");
            sb.Append(_tableName);
            sb.Append(" WHERE ");
            sb.Append(whereCondition);

            DbDataReader dr = _dbHelper.ExecuteReader(sb.ToString());

            List<T> returnValue = new List<T>();

            while (dr.Read())
            {
                model = DataUtility.SetModelValueByDataReader<T>(dr);
                returnValue.Add(model);
            }

            if (!dr.IsClosed)
            {
                dr.Close();
            }
            return returnValue;
        }

        /// <summary>
        /// get datatable by where condition
        /// </summary>
        /// <param name="_tableName">table name</param>
        /// <param name="whereCondition">where condition</param>
        /// <returns>return the datatable.</returns>
        public DataTable GetDataTable(string whereCondition)
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                throw new Exception(c_TableNull);
            }

            if (string.IsNullOrEmpty(whereCondition))
            {
                throw new Exception(c_WhereNull);
            }

            string sql = string.Format("SELECT * FROM {0} WHERE {1}", _tableName, whereCondition);

            return _dbHelper.ExecuteAdapter(sql);

        }

        #endregion

        #region add

        /// <summary>
        /// add model by primary key(primary key should be defined as identity.)
        /// </summary>
        /// <param name="_tableName">table name</param>
        /// <param name="primaryKey">primary key(Identity)</param>
        /// <param name="model">model </param>
        public void AddModel(T model)
        {
            this.AddModel( model, null);
        }

        public void AddModel(T model, List<string> identityColumns)
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                throw new Exception(c_TableNull);
            }

            if (model==null)
            {
                throw new Exception(c_PrimaryKeyNull);
            }

            //generate columns and values by input model properties.
            string columns = string.Empty;
            string values = string.Empty;
            List<PropertyInfo> properties = DataUtility.GetNotNullProperties<T>(model);
             //parameters

            int parametersCount = properties.Count;
            if (identityColumns != null)
            {
                int containedColumns = 0;
                foreach (PropertyInfo p in properties)
                {
                    if (identityColumns.Contains(p.Name))
                    {
                        containedColumns++;
                    }
                }
                parametersCount = properties.Count - containedColumns;
            }
            int parametersIndex=0;
            DbParameter[] parameters = new DbParameter[parametersCount];
            for (int i = 0; i < properties.Count; i++)
            {
                if (identityColumns==null||!identityColumns.Contains(properties[i].Name))
                {

                    parameters[parametersIndex] = Activator.CreateInstance<SqlParameter>();
                    parameters[parametersIndex].ParameterName = "@" + properties[i].Name;
                    parameters[parametersIndex].Value = properties[i].GetValue(model, null);
                    parametersIndex++;

                    if (i < properties.Count - 1)
                    {
                        columns += properties[i].Name + ",";
                        values += "@" + properties[i].Name + ",";
                    }
                    else
                    {
                        columns += properties[i].Name;
                        values += "@" + properties[i].Name;
                    }
                }
            }
            string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", _tableName,columns,values);

            _dbHelper.ExecuteNonQuery(sql, parameters);

        }

        #endregion

        #region update

        /// <summary>
        ///  update model by primary key
        /// </summary>
        /// <param name="_tableName"></param>
        /// <param name="primaryKey"></param>
        /// <param name="model"></param>
        public void UpdateModelByPrimaryKey(string primaryKey, T model,List<string> identityColumns)
        {
            string where = primaryKey+string.Format("={0}",model.GetType().GetProperty(primaryKey).GetValue(model,null));

            this.UpdateModels(model, where, identityColumns);
        }

        /// <summary>
        /// 根据主健更新实体（不包含标识列,此方法应用于主键是非标识列的表）
        /// </summary>
        /// <param name="_tableName"></param>
        /// <param name="primaryKey"></param>
        /// <param name="model"></param>
        public void UpdateModelByPrimaryKey(string primaryKey, T model)
        {
            this.UpdateModelByPrimaryKey( primaryKey, model, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="_tableName"></param>
        /// <param name="model"></param>
        /// <param name="whereCondition"></param>
        public void UpdateModels(T model, string whereCondition)
        {
            this.UpdateModels(model, whereCondition, null);
        }
        public void UpdateModels(T model,string whereCondition,List<string> identityColumns)
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                throw new Exception(c_TableNull);
            }

            if (string.IsNullOrEmpty(whereCondition))
            {
                throw new Exception(c_WhereNull);
            }

            if (model == null)
            {
                throw new Exception("model is null");
            }
            string columnAndValue = string.Empty;

            List<PropertyInfo> properties = DataUtility.GetNotNullProperties<T>(model);
            int parametersCount = properties.Count;
            if (identityColumns != null)
            {
                int containedColumns = 0;
                foreach (PropertyInfo p in properties)
                {
                    if (identityColumns.Contains(p.Name))
                    {
                        containedColumns++;
                    }
                }
                parametersCount = properties.Count - containedColumns;
            }
            int parametersIndex = 0;
            DbParameter[] parameters = new DbParameter[parametersCount];
            for (int i = 0; i < properties.Count; i++)
            {
                if (identityColumns==null||!identityColumns.Contains(properties[i].Name))
                {

                    parameters[parametersIndex] = Activator.CreateInstance<SqlParameter>();
                    parameters[parametersIndex].ParameterName = "@" + properties[i].Name;
                    parameters[parametersIndex].Value = properties[i].GetValue(model, null);
                    parametersIndex++;

                    if (i < properties.Count - 1)
                    {
                        columnAndValue+=properties[i].Name+"=@"+properties[i].Name+",";
                    }
                    else
                    {
                        columnAndValue += properties[i].Name + "=@" + properties[i].Name;
                    }
                }
            }

            string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", _tableName,columnAndValue,whereCondition);
            _dbHelper.ExecuteNonQuery(sql, parameters);
        }

        #endregion

        #region delete

        //public void DeleteModelByPrimaryKey(string _tableName, string primaryKey)
        //{

        //}

        /// <summary>
        /// 根据条件执行删除
        /// </summary>
        /// <param name="_tableName">表名，null 或 string.Empty 会引发空值异常</param>
        /// <param name="whereCondition"></param>
        public void DeleteModels(string whereCondition)
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                throw new Exception(c_TableNull);
            }

            if (string.IsNullOrEmpty(whereCondition))
            {
                throw new Exception(c_WhereNull);
            }

            string sql = string.Format("DELETE {0} WHERE {1}", _tableName, whereCondition);

            _dbHelper.ExecuteNonQuery(sql);
        }

        public void Truncate()
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                throw new Exception(c_TableNull);
            }
            string sql =string.Format("TRUNCATE TABLE {0}",_tableName);
            _dbHelper.ExecuteNonQuery(sql);
        }
        #endregion

        #endregion

        #region IDataContext<T> Members


        public DataTable GetDataTable(string whereCondition, string orderBy, int pageSize, int pageNumber)
        {
            int startNumber = pageSize * (pageNumber - 1);

            string sql = string.Format("SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER", pageSize);

            if (!string.IsNullOrEmpty(orderBy))
            {
                sql += string.Format(" (ORDER BY {0})", orderBy);
            }
            else
            {
                sql += " (ORDER BY log_id)";//默认按主键排序
            }
            sql += " AS RowNumber,* FROM "+_tableName;

            if (!string.IsNullOrEmpty(whereCondition))
            {
                sql += " where " + whereCondition;
            }

            sql += " ) _myResults WHERE RowNumber>" + startNumber.ToString();

            return _dbHelper.ExecuteAdapter(sql);
        }

        public IList<T> GetModels(string whereCondition, string orderBy, int pageSize, int pageNumber)
        {
            int startNumber = pageSize * (pageNumber - 1);

            string sql = string.Format("SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER", pageSize);

            if (!string.IsNullOrEmpty(orderBy))
            {
                sql += string.Format(" (ORDER BY {0})", orderBy);
            }
            else
            {
                sql += " (ORDER BY log_id)";//默认按主键排序
            }
            sql += " AS RowNumber,* FROM " + _tableName;

            if (!string.IsNullOrEmpty(whereCondition))
            {
                sql += " where " + whereCondition;
            }

            sql += " ) _myResults WHERE RowNumber>" + startNumber.ToString();

            DbDataReader dr = _dbHelper.ExecuteReader(sql);

            List<T> returnValue = new List<T>();

            while (dr.Read())
            {
                T model = DataUtility.SetModelValueByDataReader<T>(dr);
                returnValue.Add(model);
            }

            if (!dr.IsClosed)
            {
                dr.Close();
            }
            return returnValue;
        }

        #endregion
    }
}
